SQL Editor

When editing certain objects in Argos, you can choose to populate the data used by the object via a SQL query. For instance, a dropdown on the dashboard might use a SQL query to retrieve a list of items for users to select from. Charts, list boxes, and multi-column list boxes can use SQL to retrieve the data to be displayed.

You can create your SQL query manually, or use the Visual Design tool. Only valid SQL statements are permitted. Ensure that the SQL you create is compatible with your database. For example, when working with ANSI-compliant databases, you should not use proprietary Oracle join syntax. SQL Server queries will need to alias all fields.

This image shows the SQL Editor where you enter free-form SQL. Various operators exist on the form that can be used to create expressions.

Query Tools

Button Description
Visual Design button Create a query in the Visual Designer.
Insert Variable button Select a variable to add to the query


Operators - Use these operators to help you build your query.
= < <> > <= >= like or and not is null + - * /


Connection - Modify the connection that you will use for your query. This will permit you to run the query with a different data connection from the one that was set for the DataBlock.


Statement is a DB script/function call that returns a recordset - If you want to enter a database script or a function call that returns results, you must check this box to let Argos know to expect a result set from the script or function call. You should not check it for SQL statements that have a SELECT clause and return results-- only database scripts and function calls.

Note: When using a REF CURSOR to return results from a stored procedure in Oracle, you must ensure that your MAPS administrator has added the PLSQLRSet property to the data connection in MAPS and that its value is set to True. The value can be permanently set to True on the Driver Properties tab of the data connection, or it can be enabled for your particular user as part of a rule script.

Function that returns a ref cursor:

create or replace function func_get_cursor return sys_refcursor is
q sys_refcursor;
begin
open q for select * from spriden where spriden_last_name like 'E%' and spriden_change_ind is null;
return q;
end;

Procedure that returns a ref cursor:

create or replace procedure proc_get_cursor(q out sys_refcursor)
is
BEGIN
OPEN q FOR select * from spriden where spriden_last_name like 'E%' and spriden_change_ind is null;
END proc_get_cursor;

In MAPS, the PLSQLRSet property is added by using the green plus button to add an additional property:

The Edit Data Connection screen in MAPS. Under the Driver Properties tab, a new property called PLSQLRSet has been added in the list of properties, with its value set to True.

It can also be added for particular users via the Rule Scripts button on the User/Group Rules tab:

The User/Group Rules tab of the Edit Data Connection window, with an individual user selected and the Rule Script button highlighted

 


Edit query properties - Modify data connection settings. It is recommended that these settings are left set as their defaults unless otherwise instructed by an Evisions support technician.

SQL Operators

Operator Description
= Equal
< Less Than
<> Not Equal
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To
like Like
or Logical Or
and Logical And
not Logical Not
is Is
null Null Value
+ Addition
- Subtraction
* Multiplication
/ Division

Database Scripts and Function Calls

You can enter a database script or a function call in the SQL editor instead of SQL statements. If the script or function returns results, you must check the Statement is a database script or function call that returns a recordset option.

With a SQL SELECT statement, Argos knows that there is going to be a data set returned (because of the SELECT keyword). With database scripts and function calls, there may or may not be a result set returned. The Statement is a database script or function call that returns a recordset option must be used if the SQL statement is not in a format that is understood to return results. Checking the box lets Argos know that the script will be returning a data set, and to handle it accordingly. You may use this option with any DBMS that supports non-DML scripts (Oracle SQL Server, etc.)

As an example, suppose a function named "get_purchase_orders" exists which returns multiple rows of purchase order information. The script  {call get_purchase_orders} executes the function and returns a recordset the same as if it were returned using an SQL SELECT statement.

Testing the Query

After the query has been created, click the Next button (see figure above) which launches the Test Values dialog box shown in the figure below. In this dialog you can enter test values for all variables used in the query. For each variable, select the variable name then enter a value in the Value field. The values entered will be retained for the next time the query is tested.

This image shows the Test Values dialog box where you provide values for testing the query.

 

Need More Help?

If you need additional help creating DataBlocks, refer to the Getting Started with DataBlocks page.